package com.androturka.billintur.cyberpark;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

//public class YemekhaneDB extends SQLiteOpenHelper{
public class YemekhaneDB extends SQLiteOpenHelper{

    public static final String DATABASE_NAME 				= "yemekhane.sqlite";
    public static final String TABLE_MENU					= "menu";
    public static final String TABLE_PARAMETERS 			= "parameters";

    public static final String PARAM_WORKATBACKGROUND 		= "workAtBackGround";
    public static final String PARAM_NOTIFICATIONTIME 		= "nofitificationTime";
    public static final String PARAM_BARNOTIFICATION 		= "barNotification";
    public static final String PARAM_RINGNOTIFICATION 		= "ringNotification";
    public static final String PARAM_VIBRATIONNOTIFICATION 	= "vibrationNotification";
    

    public static final String MENU_TARIH				 	= "tarih";
    public static final String MENU_CORBA				 	= "corba";
    public static final String MENU_ANAYEMEK1				= "anayemek1";
    public static final String MENU_VEJETERYAN				= "vejeteryan";
    public static final String MENU_ANAYEMEK2				= "anayemek2";
    public static final String MENU_PILAV				 	= "pilav";
    public static final String MENU_MAKARNABOREK			= "makarnaborek";
    public static final String MENU_ZEYTINYAGLI				= "zeytinyagli";
    public static final String MENU_TATLI1				 	= "tatli1";
    public static final String MENU_TATLI2				 	= "tatli2";
    public static final String MENU_MEYVE				 	= "meyve";
    public static final String MENU_SALATA				 	= "salata";

    
    public static final String CREATE_TABLE_MENU 			= "CREATE TABLE IF NOT EXISTS "+ 
    														   TABLE_MENU + " ('"+ MENU_TARIH +"' TEXT PRIMARY KEY  NOT NULL, '"+ 
    		                                                   MENU_CORBA +"' TEXT, '"+ 
    		                                                   MENU_ANAYEMEK1 +"' TEXT, '"+ 
    		                                                   MENU_VEJETERYAN +"' TEXT, '"+ 
    		                                                   MENU_ANAYEMEK2 +"' TEXT, '"+ 
    		                                                   MENU_PILAV +"' TEXT, '"+ 
    		                                                   MENU_MAKARNABOREK +"' TEXT, '"+ 
    		                                                   MENU_ZEYTINYAGLI +"' TEXT, '"+ 
    		                                                   MENU_TATLI1 +"' TEXT, '"+ 
    		                                                   MENU_TATLI2 +"' TEXT, '"+ 
    		                                                   MENU_MEYVE +"' TEXT, '"+ 
    		                                                   MENU_SALATA +"' TEXT)";
    
    public static final String CREATE_TABLE_PARAMETERS	 	= "CREATE TABLE IF NOT EXISTS "+ TABLE_PARAMETERS + " ('"+ 
    														   PARAM_WORKATBACKGROUND +"' BOOL NOT NULL DEFAULT (1)  ,'"+ 
    														   PARAM_NOTIFICATIONTIME +"' STRING NOT NULL  DEFAULT ('1100') , '"+ 
    														   PARAM_BARNOTIFICATION +"' BOOL NOT NULL  DEFAULT 1, '"+ 
    														   PARAM_RINGNOTIFICATION +"' BOOL NOT NULL  DEFAULT 0, '" + 
    														   PARAM_VIBRATIONNOTIFICATION + "' BOOL NOT NULL  DEFAULT 0)";

	public static final String DROP_TABLE_MENU 				= "DROP TABLE IF EXISTS "+ TABLE_MENU;
	public static final String DROP_TABLE_PARAMETERS 		= "DROP TABLE IF EXISTS "+ TABLE_PARAMETERS;

    public static final int DATABASE_VERSION = 1;

    private static YemekhaneDB dbInstance;
    SQLiteDatabase dbObject;
    
    public static YemekhaneDB getInstance(Context context) {
        if (dbInstance == null) {
        	dbInstance = new YemekhaneDB(context.getApplicationContext());
        }
 
        return dbInstance;
    }

    
	public YemekhaneDB(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);

		SQLiteDatabase sqliteDB = this.getWritableDatabase();
		this.onCreate(sqliteDB);
	}
	
	public YemekhaneDB(Context context, String name, CursorFactory factory,
			int version) {
		
		super(context, name, factory, version);
		// TODO Auto-generated constructor stub
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		boolean tableParametersExists = false;
		boolean tableMenuExists = false;
		
		this.dbObject = db;

		/*
		db.execSQL(DROP_TABLE_MENU);
        db.execSQL(DROP_TABLE_PARAMETERS);
		*/
		
        tableParametersExists = isTableExists(TABLE_PARAMETERS); 
		tableMenuExists = isTableExists(TABLE_MENU); 
			
			
		if ( tableMenuExists == false )
			db.execSQL(CREATE_TABLE_MENU);
        
		if ( tableParametersExists == false )
			db.execSQL(CREATE_TABLE_PARAMETERS);

        if ( !tableParametersExists ){
    		insertParameters(1, "1145", 1, 1, 1);
        }
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
	}

	
	public boolean getVibrationNotification() {
		return getBooleanParameter( PARAM_VIBRATIONNOTIFICATION ); 
	}

	public boolean getRingNotification() {
		return getBooleanParameter( PARAM_RINGNOTIFICATION ); 
	}

	public boolean getBarNotification() {
		return getBooleanParameter( PARAM_BARNOTIFICATION ); 
	}

	public boolean getWorkAtBackGround() {
		return getBooleanParameter( PARAM_WORKATBACKGROUND ); 
	}

	public String getNotificationTime() {
		return getStringParameter( PARAM_NOTIFICATIONTIME ); 
	}

	public ArrayList<DailyMenu> getMenus() {
		ArrayList<DailyMenu> menuList = new ArrayList<DailyMenu>();
		
		String tarih = null;
		String corba = null;
		String anayemek1 = null;
		String vezeteryan = null;
		String anayemek2 = null;
		String pilav = null;
		String makarnaborek = null;
		String zeytinyagli = null;
		String tatli1 = null;
		String tatli2 = null;
		String meyve = null;
		String salata = null;
		
		
		Cursor cursor = dbObject.query(TABLE_MENU, new String[] {MENU_TARIH,MENU_CORBA,MENU_ANAYEMEK1,MENU_VEJETERYAN,MENU_ANAYEMEK2,MENU_PILAV,MENU_MAKARNABOREK,MENU_ZEYTINYAGLI,MENU_TATLI1,MENU_TATLI2,MENU_MEYVE,MENU_SALATA}, null, null, null, null, null);
		
		while ( ( cursor != null ) && cursor.moveToNext() )
		{
			tarih = cursor.getString(cursor.getColumnIndex(MENU_TARIH));
			corba = cursor.getString(cursor.getColumnIndex(MENU_CORBA));
			anayemek1 = cursor.getString(cursor.getColumnIndex(MENU_ANAYEMEK1));
			vezeteryan = cursor.getString(cursor.getColumnIndex(MENU_VEJETERYAN));
			anayemek2 = cursor.getString(cursor.getColumnIndex(MENU_ANAYEMEK2));
			pilav = cursor.getString(cursor.getColumnIndex(MENU_PILAV));
			makarnaborek = cursor.getString(cursor.getColumnIndex(MENU_MAKARNABOREK));
			zeytinyagli = cursor.getString(cursor.getColumnIndex(MENU_ZEYTINYAGLI));
			tatli1 = cursor.getString(cursor.getColumnIndex(MENU_TATLI1));
			tatli2 = cursor.getString(cursor.getColumnIndex(MENU_TATLI2));
			meyve = cursor.getString(cursor.getColumnIndex(MENU_MEYVE));
			salata = cursor.getString(cursor.getColumnIndex(MENU_SALATA));

			DailyMenu menu = new DailyMenu(tarih, corba, anayemek1,vezeteryan,anayemek2,pilav,makarnaborek,zeytinyagli,tatli1,tatli2,meyve,salata);
			menuList.add( menu );
			Log.d(Common.TAG, menu.toString());
		}

		cursor.close();
		
		return menuList;
	}

	public DailyMenu getMenu( String date) {

		String tarih = null;
		String corba = null;
		String anayemek1 = null;
		String vezeteryan = null;
		String anayemek2 = null;
		String pilav = null;
		String makarnaborek = null;
		String zeytinyagli = null;
		String tatli1 = null;
		String tatli2 = null;
		String meyve = null;
		String salata = null;
		
		
		//Cursor cursor = dbObject.query(TABLE_MENU, new String[] {MENU_TARIH,MENU_CORBA,MENU_ANAYEMEK1,MENU_VEJETERYAN,MENU_ANAYEMEK2,MENU_PILAV,MENU_MAKARNABOREK,MENU_ZEYTINYAGLI,MENU_TATLI1,MENU_TATLI2,MENU_MEYVE,MENU_SALATA}, null, null, null, null, null);
		//Cursor cursor = dbObject.query(TABLE_MENU, new String[] {MENU_TARIH,MENU_CORBA,MENU_ANAYEMEK1,MENU_VEJETERYAN,MENU_ANAYEMEK2,MENU_PILAV,MENU_MAKARNABOREK,MENU_ZEYTINYAGLI,MENU_TATLI1,MENU_TATLI2,MENU_MEYVE,MENU_SALATA}, MENU_TARIH = , null, null, null, null);
		String query = "SELECT " + MENU_TARIH + ", "+ MENU_CORBA + ", "+ MENU_ANAYEMEK1 + ", "+ MENU_VEJETERYAN + ", "+ MENU_ANAYEMEK2 + ", "+ MENU_PILAV + ", "+ MENU_MAKARNABOREK + ", "+ MENU_ZEYTINYAGLI + ", "+ MENU_TATLI1 + ", "+ MENU_TATLI2 + ", "+ MENU_MEYVE + ", "+ MENU_SALATA + " FROM "+ TABLE_MENU + " WHERE " + MENU_TARIH +" = '" + date +"'";
		Log.d(Common.TAG,query);
		
		Cursor cursor = dbObject.rawQuery(query,null);
		if ( ( cursor != null ) && cursor.moveToNext() )
		{
			tarih = cursor.getString(cursor.getColumnIndex(MENU_TARIH));
			corba = cursor.getString(cursor.getColumnIndex(MENU_CORBA));
			anayemek1 = cursor.getString(cursor.getColumnIndex(MENU_ANAYEMEK1));
			vezeteryan = cursor.getString(cursor.getColumnIndex(MENU_VEJETERYAN));
			anayemek2 = cursor.getString(cursor.getColumnIndex(MENU_ANAYEMEK2));
			pilav = cursor.getString(cursor.getColumnIndex(MENU_PILAV));
			makarnaborek = cursor.getString(cursor.getColumnIndex(MENU_MAKARNABOREK));
			zeytinyagli = cursor.getString(cursor.getColumnIndex(MENU_ZEYTINYAGLI));
			tatli1 = cursor.getString(cursor.getColumnIndex(MENU_TATLI1));
			tatli2 = cursor.getString(cursor.getColumnIndex(MENU_TATLI2));
			meyve = cursor.getString(cursor.getColumnIndex(MENU_MEYVE));
			salata = cursor.getString(cursor.getColumnIndex(MENU_SALATA));

			if ( tarih != null ){
				
				DailyMenu menu = new DailyMenu(tarih, corba, anayemek1,vezeteryan,anayemek2,pilav,makarnaborek,zeytinyagli,tatli1,tatli2,meyve,salata);
				Log.d("DB", menu.toString());
				
				return menu;
			}	
		}

		cursor.close();
		
		return null;
	}


	public int getMenuCount() {

		String query = "SELECT COUNT(*) FROM " + TABLE_MENU;
		Log.d(Common.TAG,query);
		
		Cursor cursor = dbObject.rawQuery(query,null);
		cursor.moveToFirst();
		int count= cursor.getInt(0);
		
		Log.d(Common.TAG, "Menu Count " + count);
		cursor.close();
		
		return count;
	}

	public void updateVibrationNotification( boolean vibrationNotification ) {
		updateBooleanParameter( PARAM_VIBRATIONNOTIFICATION, vibrationNotification);
	}

	public void updateRingNotificaiton( boolean ringNotification ) {
		updateBooleanParameter( PARAM_RINGNOTIFICATION, ringNotification);
	}

	public void updateBarNotification( boolean barNotification) {
		updateBooleanParameter( PARAM_BARNOTIFICATION, barNotification);
	}

	public void updateWorkAtBackGround( boolean workAtWorkGround) {
		updateBooleanParameter( PARAM_WORKATBACKGROUND, workAtWorkGround);
	}

	public void updateNotificationTime( String notificationTime) {
		updateStringParameter( PARAM_NOTIFICATIONTIME, notificationTime);
	}
	
	private boolean getBooleanParameter( String parameter){
		
		Cursor cursor = dbObject.query(TABLE_PARAMETERS, new String[] {parameter}, null, null, null, null, null);
		
		if ( cursor.moveToFirst() )
		{
			boolean value =cursor.getString(0).contains("1");
			Log.d("DB", parameter + " " + value );
			cursor.close();
			return value;
		}
		else
		{	
			cursor.close();
			return false;
		}	
	}

	@SuppressWarnings("unused")
	private int getIntegerParameter(String parameter) {
		Cursor cursor = dbObject.query(TABLE_PARAMETERS, new String[] {parameter}, null, null, null, null, null);
		
		if ( cursor.moveToFirst() )
		{
			int value;
			value = cursor.getInt(0);
			Log.d("DB", parameter + " " + value );
			cursor.close();
			return value;
		}
		else
		{	
			cursor.close();
			return 0;			
		}	
	}

	private String getStringParameter( String parameter) {
		Cursor cursor = dbObject.query(TABLE_PARAMETERS, new String[] {parameter}, null, null, null, null, null);
		
		if ( cursor.moveToFirst() )
		{
			String value;
			value = cursor.getString(0);
			Log.d("DB", parameter + " " + value );
			cursor.close();
			return value;
		}
		else	
		{	
			cursor.close();
			return null;			
		}	
	}

	private void updateBooleanParameter( String parameter, boolean value) {
	    ContentValues args = new ContentValues();
	    args.put(parameter, value);
	    dbObject.update(TABLE_PARAMETERS, args, null, null);
	}

	@SuppressWarnings("unused")
	private void updateIntegerParameter( String parameter, int value) {
	    ContentValues args = new ContentValues();
	    args.put(parameter, value);
	    dbObject.update(TABLE_PARAMETERS, args, null, null);		
	}

	private void updateStringParameter( String parameter, String value) {
	    ContentValues args = new ContentValues();
	    Log.d("DB","updateStringParameter " + parameter + " " + value);
	    args.put(parameter, value);
	    dbObject.update(TABLE_PARAMETERS, args, null, null);
	}
	
	public void insertMenu( String tarih, String corba, String anayemek1,String vezeteryan,String anayemek2,String pilav,String makarnaborek,String zeytinyagli,String tatli1,String tatli2,String meyve,String salata )
	{
		String sqlString = "INSERT INTO " + TABLE_MENU + " VALUES ( '" + tarih + "', '" + corba + "' , '" + anayemek1 + "' , '" + vezeteryan + "' , '" + anayemek2 + "' , '" + pilav + "' , '" + makarnaborek + "' , '" + zeytinyagli + "' , '" + tatli1 + "' , '" + tatli2 + "' , '" + meyve + "' , '" + salata + "' )"; 
		Log.d("DB",sqlString);
		dbObject.execSQL(sqlString); 		
	}

	public void insertMenu( DailyMenu menu )
	{
		String sqlString = "INSERT INTO " + TABLE_MENU + " VALUES ( '" + menu.getTarih() + "', '" + menu.getCorba() + "' , '" + menu.getAnayemek1() + "' , '" + menu.getVejeteryan() + "' , '" + menu.getAnayemek2()+ "' , '" + menu.getPilav() + "' , '" + menu.getMakarnaborek() + "' , '" + menu.getZeytinyagli() + "' , '" + menu.getTatli1() + "' , '" + menu.getTatli2() + "' , '" + menu.getMeyve() + "' , '" + menu.getSalata() + "' )"; 
		Log.d("DB",sqlString);
		dbObject.execSQL(sqlString);
	}

	public void deleteMenu(String tarih )
	{
		String sqlString = "DELETE FROM " + TABLE_MENU + " WHERE " + MENU_TARIH + " = " + tarih; 
		Log.d("DB",sqlString);
		dbObject.execSQL(sqlString); 
	}

	public void deleteAllMenus()
	{
		String sqlString = "DELETE FROM " + TABLE_MENU; 
		Log.d("DB",sqlString);
		dbObject.execSQL(sqlString);
	}

	public void deleteParameters()
	{
		String sqlString = "DELETE FROM " + TABLE_PARAMETERS; 
		Log.d("DB",sqlString);
		dbObject.execSQL(sqlString); 
	}
	
	public void insertParameters(int workAtBackGround, String notificationTime, int barNotification, int ringNotification, int vibrationNotification )
	{
		String sqlString = "INSERT INTO " + TABLE_PARAMETERS + " VALUES ( '" + workAtBackGround + "', '" + notificationTime + "', '" + barNotification + "', '" + ringNotification + "', '" + vibrationNotification + "' )"; 
		Log.d("DB",sqlString);
		dbObject.execSQL(sqlString); 		
	}
	
	boolean isTableExists( String tableName )
	{
	    if (tableName == null || dbObject == null || !dbObject.isOpen())
	    {
	        return false;
	    }
	    Cursor cursor = dbObject.rawQuery("SELECT COUNT(*) FROM sqlite_master WHERE type = ? AND name = ?", new String[] {"table", tableName});
	    if (!cursor.moveToFirst())
	    {
		    cursor.close();
	        return false;
	    }
	    int count = cursor.getInt(0);
	    cursor.close();
	    return count > 0;
	}

	protected void finalize ()  {
        dbObject.close();
    }
}
